Importing librarys¶

import pandas as pd
import numpy as np

import plotly.express as px
import plotly.io as pio
pio.renderers.default='notebook'

import matplotlib.pyplot as plt
%matplotlib inline

from sklearn.cluster import KMeans
from sklearn.preprocessing import MinMaxScaler

pd.options.display.max_columns = 100
pd.options.display.max_rows = 100

Import data¶

Import calendar¶

calendar = pd.read_csv('../data/calendar.csv')

calendar.dtypes

listing_id     int64
date          object
available     object
price         object
dtype: object

calendar.isnull().sum()

listing_id         0
date               0
available          0
price         459028
dtype: int64

Formatting data¶

"date" to datetime¶

calendar.date = pd.to_datetime(calendar.date)

"price" to float¶

calendar.price = calendar.price.str.replace('\$|\,', '').astype(float)

calendar.head(1)

Import listings¶

listings = pd.read_csv('../data/listings.csv')

listings[['id', 'last_scraped', 'name', 'description', 'neighborhood_overview', 'host_id', 'host_name', 'host_since',
 'street', 'neighbourhood', 'neighbourhood_cleansed', 'neighbourhood_group_cleansed', 'city', 'state',
 'latitude', 'longitude', 'property_type', 'room_type', 'accommodates', 'bathrooms', 'bedrooms', 'beds',
 'bed_type', 'amenities', 'price', 'number_of_reviews', 'first_review', 'last_review', 'review_scores_rating',
 'review_scores_accuracy', 'review_scores_location', 'reviews_per_month']].dtypes

id                                int64
last_scraped                     object
name                             object
description                      object
neighborhood_overview            object
host_id                           int64
host_name                        object
host_since                       object
street                           object
neighbourhood                    object
neighbourhood_cleansed           object
neighbourhood_group_cleansed     object
city                             object
state                            object
latitude                        float64
longitude                       float64
property_type                    object
room_type                        object
accommodates                      int64
bathrooms                       float64
bedrooms                        float64
beds                            float64
bed_type                         object
amenities                        object
price                            object
number_of_reviews                 int64
first_review                     object
last_review                      object
review_scores_rating            float64
review_scores_accuracy          float64
review_scores_location          float64
reviews_per_month               float64
dtype: object

Formatting data¶

"price" to float¶

listings.price = listings.price.str.replace('\$|\,', '').astype(float)

listings.shape

(3818, 92)

listings.sample(1)

Import reviews¶

reviews = pd.read_csv('../data/reviews.csv')

reviews.dtypes

listing_id        int64
id                int64
date             object
reviewer_id       int64
reviewer_name    object
comments         object
dtype: object

reviews.head(1)

Scanning the data¶

Number unique listings¶

len(listings.id.unique())

3818

Number of hosts¶

len(listings.host_id.unique())

2751

Neighbourhood columns¶

listings[['neighbourhood', 'neighbourhood_cleansed', 'neighbourhood_group_cleansed']].isnull().sum()

neighbourhood                   416
neighbourhood_cleansed            0
neighbourhood_group_cleansed      0
dtype: int64

Business Questions¶

When is the most likely time for you to visit Seattle?¶

To answer the first question we are going to look at the number of available rooms by month¶

Number of non-available rooms by month¶

calendar[calendar.available == 'f'].groupby(calendar.date.dt.month).listing_id.nunique().reset_index().rename(
columns= {'listing_id': 'Non-available rooms'}).head(1)

fig = px.line(
    calendar[calendar.available == 'f'].groupby(calendar.date.dt.month).listing_id.nunique().reset_index(
    ).rename(columns= {'listing_id': 'Non-available rooms'}),
    x= 'date', y= 'Non-available rooms', title= 'Number of accommodation rented at least once by month')
fig.show()

Number of days rented per month¶

calendar[calendar.available == 'f'].groupby(calendar.date.dt.month).listing_id.count().reset_index(
).rename(columns= {'listing_id': 'Number of days rented'}).head(1)

fig = px.line(
    calendar[calendar.available == 'f'].groupby(calendar.date.dt.month).listing_id.count().reset_index(
).rename(columns= {'listing_id': 'Number of days rented'}),
    x= 'date', y= 'Number of days rented', title= 'Number of days rented by month')
fig.show()

Answers:¶

The busiest months to visit Seattle are January, June, July and August.
- We can also see that in january we have a great number of rooms rented for a long period;
- But in the summer (June, july and august) we see less rooms rented for more days.

How can we group your properties in order to find local peculiarities?¶

# Create Figure
px.set_mapbox_access_token(open("../mapbox_token.txt").read())
fig = px.scatter_mapbox(listings.sample(500),
    lat= "latitude", lon= "longitude", color= "price", hover_name= 'id',
    zoom=10, width= 550, height= 650, title= 'Price by accommodation')

fig.show()

plt.Figure()
listings.price.plot.hist(bins= 50, figsize= (15, 5))
plt.xlabel('Price')
plt.ylabel('Frequency');
#plt.savefig('frequency_price.png')

distortions = []
K = range(1, 20)
for k in K:
    kmeanModel = KMeans(n_clusters= k)
    scaler = MinMaxScaler()
    kmeanModel.fit(scaler.fit_transform(listings[['latitude', 'longitude', 'price']]))
    distortions.append(kmeanModel.inertia_)

plt.figure(figsize=(16,8))
plt.plot(K, distortions, 'bx-')
plt.xlabel('k')
plt.ylabel('Distortion')
plt.title('The Elbow Method showing the optimal k')
#plt.savefig('elbow_method.png')
plt.show()

# Create K-Means
k = 6
kmeanModel = KMeans(n_clusters=k)
scaler = MinMaxScaler()
kmeanModel.fit(scaler.fit_transform(listings[['latitude', 'longitude', 'price']]))

# Execute clusters
listings['price_cluster'] = kmeanModel.predict(scaler.transform(listings[['latitude', 'longitude', 'price']]))

# Create Figure
import plotly.express as px
px.set_mapbox_access_token(open("../mapbox_token.txt").read())
fig = px.scatter_mapbox(
    lat= listings["latitude"], lon= listings["longitude"], color= listings["price_cluster"].astype(str),
    zoom= 10, width= 550, height= 650)

fig.show()

# Create K-Means
k = 7
kmeanModel = KMeans(n_clusters=k)
scaler = MinMaxScaler()
kmeanModel.fit(scaler.fit_transform(listings[['latitude', 'longitude', 'price']]))

# Execute clusters
listings['price_cluster'] = kmeanModel.predict(scaler.transform(listings[['latitude', 'longitude', 'price']]))

# Create Figure
import plotly.express as px
px.set_mapbox_access_token(open("../mapbox_token.txt").read())
fig = px.scatter_mapbox(
    lat= listings["latitude"], lon= listings["longitude"], color= listings["price_cluster"].astype(str),
    zoom= 10, width= 550, height= 650)

fig.show()

listings.groupby(['price_cluster']).agg(
    {
        'id': 'count',
        'price': ['min', 'max', 'mean'],
        'accommodates': 'mean',
        'bathrooms': 'mean',
        'bedrooms': 'mean',
        'beds': 'mean'
    }
).round(1)

At what time do hosts tend to raise prices and which regions have the biggest price rises?¶

To answer this question we are going to look at the mean price of the available rooms by month¶

calendar[calendar.available == 't'].groupby(calendar.date.dt.month).price.mean().reset_index().head(1)

fig = px.line(
    calendar[calendar.available == 't'].groupby(calendar.date.dt.month).price.mean().reset_index(
    ).rename(columns= {'date': 'Month', 'price': 'Mean Price'}),
    x= 'Month', y= 'Mean Price', title= 'Mean Price')
fig.show()

Since we don't have the price of the non-available rooms we can also look at the difference between the actual price of the room and the max price register by month.¶

max_price_by_month = calendar.groupby([calendar.listing_id, calendar.date.dt.month]).price.max().reset_index()

max_price_by_month = max_price_by_month.merge(listings[['id', 'price']], how= 'left', left_on= 'listing_id', right_on= 'id')[[
    'listing_id', 'date', 'price_x', 'price_y']].rename(columns= {'price_x': 'room_price_by_month', 'price_y': 'room_price'})

max_price_by_month['price_growth'] = max_price_by_month['room_price_by_month'] / max_price_by_month['room_price']

max_price_by_month.groupby(['date']).price_growth.mean().reset_index().head(1)

fig = px.line(
    max_price_by_month.groupby(['date']).price_growth.mean().reset_index(
    ).rename(columns= {'date': 'Month', 'price_growth': 'Price Growth'}),
    x= 'Month', y= 'Price Growth', title= 'Price Growth by Month')
fig.show()

max_price_by_neighbourhood = max_price_by_month.merge(
    listings[['id', 'neighbourhood_cleansed']],
    how= 'left',
    left_on= 'listing_id',
    right_on= 'id'
).drop(
    columns = ['id']
)

max_price_by_neighbourhood.groupby('neighbourhood_cleansed').price_growth.max(
).sort_values(ascending= False).reset_index().head(1)

Filtering the top 10¶

top_price_growth_neighbourhood = max_price_by_neighbourhood.groupby('neighbourhood_cleansed').price_growth.max(
).sort_values(ascending= False).reset_index().head(10).neighbourhood_cleansed.to_list()

px.set_mapbox_access_token(open("../mapbox_token.txt").read())
fig = px.scatter_mapbox( #[listings.neighbourhood_cleansed == 'Georgetown']
    listings[listings.neighbourhood_cleansed.isin(top_price_growth_neighbourhood)].groupby(
        'neighbourhood_cleansed')[['latitude', 'longitude', 'price']].mean().reset_index(),
    lat= "latitude", lon= "longitude", color= 'price', size= 'price',
    zoom= 10, width= 550, height= 650, title= 'Top 10 neighborhoods with the highest price increase')

fig.show()

	id	price			accommodates	bathrooms	bedrooms	beds
	count	min	max	mean	mean	mean	mean	mean
price_cluster
0	282	30.0	495.0	123.8	3.5	1.3	1.5	1.9
1	800	35.0	290.0	132.7	3.3	1.2	1.1	1.7
2	1156	22.0	400.0	119.4	3.0	1.2	1.1	1.5
3	598	20.0	375.0	110.5	3.3	1.2	1.4	1.8
4	285	25.0	375.0	90.9	3.2	1.2	1.3	1.6
5	146	275.0	1000.0	434.6	7.3	2.4	3.1	3.8
6	551	28.0	400.0	98.2	3.1	1.2	1.3	1.7